const express = require('express');
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const router = express.Router();
const { authenticateToken } = require('../middleware/auth');
const { query, getPool } = require('../config/database');
// const { aiHelper } = require('../utils/aiHelper');

// 配置multer用于文件上传
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    const uploadDir = path.join(__dirname, '../../uploads/schedule');
    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true });
    }
    cb(null, uploadDir);
  },
  filename: function (req, file, cb) {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, file.fieldname + '-' + uniqueSuffix + path.extname(file.originalname));
  }
});

const upload = multer({ 
  storage: storage,
  limits: {
    fileSize: 10 * 1024 * 1024 // 10MB
  },
  fileFilter: function (req, file, cb) {
    const allowedTypes = [
      'application/pdf',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', // .xlsx
      'application/vnd.ms-excel', // .xls
      'application/vnd.openxmlformats-officedocument.wordprocessingml.document', // .docx
      'application/msword' // .doc
    ];
    
    if (allowedTypes.includes(file.mimetype)) {
      cb(null, true);
    } else {
      cb(new Error('只支持 PDF、Excel、Word 格式的文件'), false);
    }
  }
});

// 上传课表文件
router.post('/upload', authenticateToken, upload.single('file'), async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({
        success: false,
        message: '请选择要上传的文件'
      });
    }

    // 处理文件名编码问题
    let fileName = req.file.originalname;
    try {
      // 尝试解码文件名
      fileName = Buffer.from(fileName, 'latin1').toString('utf8');
    } catch (error) {
      console.log('文件名解码失败，使用原始名称:', fileName);
    }

    const fileInfo = {
      fileName: fileName,
      fileSize: req.file.size,
      fileType: req.file.mimetype,
      uploadId: req.file.filename,
      filePath: req.file.path
    };

    res.json({
      success: true,
      message: '文件上传成功',
      data: fileInfo
    });
  } catch (error) {
    console.error('文件上传失败:', error);
    res.status(500).json({
      success: false,
      message: '文件上传失败: ' + error.message
    });
  }
});

// AI生成课表
router.post('/generate', authenticateToken, async (req, res) => {
  try {
    const { fileId, startWeek, semester, totalWeeks, startDate } = req.body;

    if (!fileId) {
      return res.status(400).json({
        success: false,
        message: '缺少文件ID参数'
      });
    }

    // 查找上传的文件
    const filePath = path.join(__dirname, '../../uploads/schedule', fileId);
    if (!fs.existsSync(filePath)) {
      return res.status(404).json({
        success: false,
        message: '文件不存在'
      });
    }

    // 调用AI分析文件内容
    console.log('开始分析文件:', filePath);
    console.log('智能分析模式：AI将自动提取所有必要信息');
    
    const analysisResult = await analyzeScheduleFile(filePath, {
      // AI将自动从表格中提取这些信息，无需手动传入
      startWeek: startWeek || 1,
      semester: semester || '',
      totalWeeks: totalWeeks || 0,
      startDate: startDate || ''
    });
    
    console.log('分析完成，结果:', analysisResult);

    res.json({
      success: true,
      message: 'AI分析完成',
      data: analysisResult
    });
  } catch (error) {
    console.error('AI分析失败:', error);
    res.status(500).json({
      success: false,
      message: 'AI分析失败: ' + error.message
    });
  }
});

// 保存课表
router.post('/save', authenticateToken, async (req, res) => {
  try {
    const scheduleData = req.body;
    const userId = req.user.id;

    console.log('开始保存课表到数据库:', scheduleData);

    // 首先检查schedules表是否存在
    try {
      const tableCheck = await query('SHOW TABLES LIKE "schedules"');
      console.log('schedules表检查结果:', tableCheck);
      
      if (tableCheck.length === 0) {
        console.log('schedules表不存在，无法保存课表');
        return res.status(500).json({
          success: false,
          message: '数据库表不存在，请先创建课表相关表'
        });
      }
    } catch (tableError) {
      console.error('检查schedules表失败:', tableError);
      return res.status(500).json({
        success: false,
        message: '数据库连接失败'
      });
    }

    // 开始数据库事务
    const pool = await getPool();
    const connection = await pool.getConnection();
    await connection.beginTransaction();

    try {
      // 1. 保存课表主信息
      const scheduleResult = await connection.execute(
        `INSERT INTO schedules (user_id, title, semester, start_week, total_weeks, start_date, end_date, class_times, duration, description) 
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          userId,
          scheduleData.title || `${scheduleData.semester}课表`,
          scheduleData.semester || '2024春季',
          scheduleData.startWeek || 1,
          scheduleData.totalWeeks || 20,
          scheduleData.startDate || null,
          scheduleData.endDate || null,
          JSON.stringify(scheduleData.classTimes || {}),
          scheduleData.duration || 45,
          scheduleData.description || null
        ]
      );

      const scheduleId = scheduleResult[0].insertId;
      console.log('课表主信息保存成功，ID:', scheduleId);

      // 2. 保存课程信息
      if (scheduleData.courses && scheduleData.courses.length > 0) {
        for (const course of scheduleData.courses) {
          // 保存课程基本信息
          const courseResult = await connection.execute(
            `INSERT INTO courses (schedule_id, name, teacher, classroom, day_of_week, time_slot, duration, credits, course_type, description, cell_position) 
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [
              scheduleId,
              course.name || '未命名课程',
              course.teacher || '待定',
              course.classroom || '待定',
              course.dayOfWeek || 1,
              course.timeSlot || 1,
              course.duration || 2,
              course.credits || 3.0,
              course.type || 'required',
              course.description || null,
              course.cellPosition || null
            ]
          );

          const courseId = courseResult[0].insertId;
          console.log('课程保存成功，ID:', courseId, '名称:', course.name);

          // 保存课程周次信息
          if (course.weeks && course.weeks.length > 0) {
            for (const week of course.weeks) {
              await connection.execute(
                `INSERT INTO course_weeks (course_id, week_number) VALUES (?, ?)`,
                [courseId, week]
              );
            }
            console.log(`课程 ${course.name} 的周次信息保存完成，共 ${course.weeks.length} 周`);
          }
        }
      }

      // 3. 保存分析记录（如果有的话）
      if (scheduleData.analysisData) {
        await connection.execute(
          `INSERT INTO schedule_analyses (schedule_id, file_name, file_path, file_size, analysis_type, confidence, table_structure, semester_info, suggestions, warnings, questions, analysis_log) 
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
          [
            scheduleId,
            scheduleData.analysisData.fileName || '未知文件',
            scheduleData.analysisData.filePath || '',
            scheduleData.analysisData.fileSize || 0,
            'ai',
            scheduleData.confidence || 0.0,
            JSON.stringify(scheduleData.tableStructure || {}),
            JSON.stringify(scheduleData.semesterInfo || {}),
            JSON.stringify(scheduleData.suggestions || []),
            JSON.stringify(scheduleData.warnings || []),
            JSON.stringify(scheduleData.questions || []),
            JSON.stringify(scheduleData.analysisLog || {})
          ]
        );
        console.log('分析记录保存成功');
      }

      // 提交事务
      await connection.commit();
      console.log('课表保存事务提交成功');

      res.json({
        success: true,
        message: '课表保存成功',
        data: {
          id: scheduleId,
          userId,
          coursesCount: scheduleData.courses ? scheduleData.courses.length : 0
        }
      });

    } catch (dbError) {
      // 回滚事务
      await connection.rollback();
      console.error('数据库操作失败，已回滚:', dbError);
      throw dbError;
    } finally {
      connection.release();
    }

  } catch (error) {
    console.error('保存课表失败:', error);
    res.status(500).json({
      success: false,
      message: '保存课表失败: ' + error.message
    });
  }
});

// 获取用户课表
router.get('/user', authenticateToken, async (req, res) => {
  try {
    const userId = req.user.id;

    console.log('获取用户课表，用户ID:', userId);

    // 首先检查schedules表是否存在
    try {
      const tableCheck = await query('SHOW TABLES LIKE "schedules"');
      console.log('schedules表检查结果:', tableCheck);
      
      if (tableCheck.length === 0) {
        console.log('schedules表不存在，返回空数据');
        return res.json({
          success: true,
          message: '获取课表成功',
          data: []
        });
      }
    } catch (tableError) {
      console.error('检查schedules表失败:', tableError);
      return res.json({
        success: true,
        message: '获取课表成功',
        data: []
      });
    }

    // 查询用户的所有课表
    const schedules = await query(
      `SELECT id, title, semester, start_week, total_weeks, start_date, end_date, class_times, duration, description, is_active, created_at, updated_at 
       FROM schedules 
       WHERE user_id = ? AND is_active = TRUE 
       ORDER BY created_at DESC`,
      [userId]
    );

    console.log('查询到课表数量:', schedules.length);
    console.log('课表数据:', schedules);

    // 为每个课表查询课程信息
    const schedulesWithCourses = await Promise.all(
      schedules.map(async (schedule) => {
        // 查询课程信息
        const courses = await query(
          `SELECT c.id, c.name, c.teacher, c.classroom, c.day_of_week, c.time_slot, c.duration, c.credits, c.course_type, c.description, c.cell_position, c.is_active
           FROM courses c 
           WHERE c.schedule_id = ? AND c.is_active = TRUE 
           ORDER BY c.day_of_week, c.time_slot`,
          [schedule.id]
        );

        // 为每个课程查询周次信息
        const coursesWithWeeks = await Promise.all(
          courses.map(async (course) => {
            const weeks = await query(
              `SELECT week_number FROM course_weeks WHERE course_id = ? ORDER BY week_number`,
              [course.id]
            );

            return {
              id: course.id.toString(),
              name: course.name,
              teacher: course.teacher,
              classroom: course.classroom,
              dayOfWeek: course.day_of_week,
              timeSlot: course.time_slot,
              duration: course.duration,
              credits: course.credits,
              type: course.course_type,
              description: course.description,
              cellPosition: course.cell_position,
              weeks: weeks.map(w => w.week_number)
            };
          })
        );

        return {
          id: schedule.id.toString(),
          userId: schedule.user_id,
          title: schedule.title,
          semester: schedule.semester,
          startWeek: schedule.start_week,
          totalWeeks: schedule.total_weeks,
          startDate: schedule.start_date,
          endDate: schedule.end_date,
          classTimes: schedule.class_times || {},
          duration: schedule.duration,
          description: schedule.description,
          courses: coursesWithWeeks,
          createdAt: schedule.created_at,
          updatedAt: schedule.updated_at
        };
      })
    );

    res.json({
      success: true,
      message: '获取课表成功',
      data: schedulesWithCourses
    });

  } catch (error) {
    console.error('获取课表失败:', error);
    res.status(500).json({
      success: false,
      message: '获取课表失败: ' + error.message
    });
  }
});

// 更新课程信息
router.put('/course/:courseId', authenticateToken, async (req, res) => {
  try {
    const { courseId } = req.params;
    const courseData = req.body;
    const userId = req.user.id;

    console.log('更新课程信息，课程ID:', courseId, '数据:', courseData);

    // 验证课程是否属于当前用户
    const course = await query(
      `SELECT c.id, c.schedule_id, s.user_id 
       FROM courses c 
       JOIN schedules s ON c.schedule_id = s.id 
       WHERE c.id = ? AND s.user_id = ?`,
      [courseId, userId]
    );

    if (!course || course.length === 0) {
      return res.status(404).json({
        success: false,
        message: '课程不存在或无权限'
      });
    }

    // 开始数据库事务
    const pool = await getPool();
    const connection = await pool.getConnection();
    await connection.beginTransaction();

    try {
      // 更新课程基本信息
      await connection.execute(
        `UPDATE courses 
         SET name = ?, teacher = ?, classroom = ?, day_of_week = ?, time_slot = ?, duration = ?, credits = ?, course_type = ?, description = ?, cell_position = ?, updated_at = NOW()
         WHERE id = ?`,
        [
          courseData.name,
          courseData.teacher,
          courseData.classroom,
          courseData.dayOfWeek,
          courseData.timeSlot,
          courseData.duration,
          courseData.credits,
          courseData.type,
          courseData.description,
          courseData.cellPosition,
          courseId
        ]
      );

      // 更新周次信息
      if (courseData.weeks && courseData.weeks.length > 0) {
        // 删除原有周次
        await connection.execute(
          `DELETE FROM course_weeks WHERE course_id = ?`,
          [courseId]
        );

        // 插入新周次
        for (const week of courseData.weeks) {
          await connection.execute(
            `INSERT INTO course_weeks (course_id, week_number) VALUES (?, ?)`,
            [courseId, week]
          );
        }
      }

      await connection.commit();

      res.json({
        success: true,
        message: '课程信息更新成功',
        data: { courseId, ...courseData }
      });

    } catch (dbError) {
      await connection.rollback();
      throw dbError;
    } finally {
      connection.release();
    }

  } catch (error) {
    console.error('更新课程失败:', error);
    res.status(500).json({
      success: false,
      message: '更新课程失败: ' + error.message
    });
  }
});

// 删除课程
router.delete('/course/:courseId', authenticateToken, async (req, res) => {
  try {
    const { courseId } = req.params;
    const userId = req.user.id;

    console.log('删除课程，课程ID:', courseId);

    // 验证课程是否属于当前用户
    const course = await query(
      `SELECT c.id, c.schedule_id, s.user_id 
       FROM courses c 
       JOIN schedules s ON c.schedule_id = s.id 
       WHERE c.id = ? AND s.user_id = ?`,
      [courseId, userId]
    );

    if (!course || course.length === 0) {
      return res.status(404).json({
        success: false,
        message: '课程不存在或无权限'
      });
    }

    // 开始数据库事务
    const pool = await getPool();
    const connection = await pool.getConnection();
    await connection.beginTransaction();

    try {
      // 删除课程周次信息
      await connection.execute(
        `DELETE FROM course_weeks WHERE course_id = ?`,
        [courseId]
      );

      // 删除课程（软删除）
      await connection.execute(
        `UPDATE courses SET is_active = FALSE, updated_at = NOW() WHERE id = ?`,
        [courseId]
      );

      await connection.commit();

      res.json({
        success: true,
        message: '课程删除成功'
      });

    } catch (dbError) {
      await connection.rollback();
      throw dbError;
    } finally {
      connection.release();
    }

  } catch (error) {
    console.error('删除课程失败:', error);
    res.status(500).json({
      success: false,
      message: '删除课程失败: ' + error.message
    });
  }
});

// 删除课表
router.delete('/:scheduleId', authenticateToken, async (req, res) => {
  try {
    const { scheduleId } = req.params;
    const userId = req.user.id;

    console.log('删除课表，课表ID:', scheduleId);

    // 验证课表是否属于当前用户
    const schedule = await query(
      `SELECT id, user_id FROM schedules WHERE id = ? AND user_id = ?`,
      [scheduleId, userId]
    );

    if (!schedule || schedule.length === 0) {
      return res.status(404).json({
        success: false,
        message: '课表不存在或无权限'
      });
    }

    // 开始数据库事务
    const pool = await getPool();
    const connection = await pool.getConnection();
    await connection.beginTransaction();

    try {
      // 软删除课表
      await connection.execute(
        `UPDATE schedules SET is_active = FALSE, updated_at = NOW() WHERE id = ?`,
        [scheduleId]
      );

      // 软删除所有相关课程
      await connection.execute(
        `UPDATE courses SET is_active = FALSE, updated_at = NOW() WHERE schedule_id = ?`,
        [scheduleId]
      );

      await connection.commit();

      res.json({
        success: true,
        message: '课表删除成功'
      });

    } catch (dbError) {
      await connection.rollback();
      throw dbError;
    } finally {
      connection.release();
    }

  } catch (error) {
    console.error('删除课表失败:', error);
    res.status(500).json({
      success: false,
      message: '删除课表失败: ' + error.message
    });
  }
});

// 重新分析课表
router.post('/reanalyze', authenticateToken, async (req, res) => {
  try {
    const { fileId, ...options } = req.body;

    // 查找上传的文件
    const filePath = path.join(__dirname, '../../uploads/schedule', fileId);
    if (!fs.existsSync(filePath)) {
      return res.status(404).json({
        success: false,
        message: '文件不存在'
      });
    }

    // 重新调用AI分析
    const analysisResult = await analyzeScheduleFile(filePath, options);

    res.json({
      success: true,
      message: '重新分析完成',
      data: analysisResult
    });
  } catch (error) {
    console.error('重新分析失败:', error);
    res.status(500).json({
      success: false,
      message: '重新分析失败: ' + error.message
    });
  }
});

// AI分析文件内容的函数
async function analyzeScheduleFile(filePath, options) {
  try {
    console.log('开始AI分析课表文件:', filePath);
    console.log('分析参数:', options);
    
    const fs = require('fs');
    const path = require('path');
    const XLSX = require('xlsx');
    
    // 解析Excel文件
    const ext = path.extname(filePath).toLowerCase();
    if (ext !== '.xlsx' && ext !== '.xls') {
      throw new Error('只支持Excel文件格式');
    }
    
    console.log('解析Excel文件...');
    const workbook = XLSX.readFile(filePath);
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    
    // 第一步：取消所有合并单元格，获取完整的表格数据
    const unmergedData = unmergeCells(worksheet);
    console.log('取消合并单元格后的数据行数:', unmergedData.length);
    
    // 第二步：AI分析表格结构，识别四大区域
    const structurePrompt = `请分析以下Excel课表的结构，识别四大区域：
1. 星期区域：包含星期几信息的行（如A2:E2）
2. 节次区域：包含节次信息的列（可能是单列A3:A12，也可能是两列A3:A12和B3:B12）
3. 课程区域：包含课程信息的区域（如C3:H8）
4. 备注区域：包含备注信息的区域（如A1:H1）

特别注意：
- 节次区域可能有两列：一列显示上午/下午，另一列显示第几节课
- 如果节次区域有多列，需要将同一行两列的数据合并为一个节次信息
- 课程区域是包含所有课程数据的核心区域
- 备注区域可能包含重要的课程信息

Excel表格内容：
${unmergedData.map((row, index) => `第${index + 1}行: ${row.join(' | ')}`).join('\n')}

请返回JSON格式：
{
  "weekArea": "A2:E2",
  "timeArea": "A3:A12", 
  "timeArea2": "B3:B12",
  "courseArea": "C3:H8",
  "notesArea": "A1:H1"
}`;

    const structureResult = await callAliyunAI(structurePrompt);
    console.log('表格结构分析结果:', structureResult);
    
    // 第三步：处理节次区域多列合并单元格的情况
    const processedTimeData = processTimeArea(unmergedData, structureResult);
    console.log('处理后的节次数据:', processedTimeData);
    
    // 第四步：基于表格结构提取课程信息
    const courses = await extractCoursesFromStructure(unmergedData, structureResult, processedTimeData);
    console.log('提取的课程数量:', courses.length);
    
    // 第五步：AI分析学期信息
    const semesterPrompt = `请从以下课表信息中提取学期信息：
${unmergedData.map((row, index) => `第${index + 1}行: ${row.join(' | ')}`).join('\n')}

请返回JSON格式：
{
  "semester": "2024春季",
  "startDate": "2024-02-26",
  "endDate": "2024-06-30",
  "totalWeeks": 18,
  "classTimes": {
    "morning": "08:00-08:45",
    "afternoon": "14:00-14:45", 
    "evening": "19:00-19:45"
  },
  "duration": 45
}`;

    const semesterResult = await callAliyunAI(semesterPrompt);
    console.log('学期信息分析结果:', semesterResult);
    
    return {
      semesterInfo: semesterResult,
      tableStructure: structureResult,
      courses: courses,
      confidence: 0.85,
      suggestions: [
        '请检查课程时间是否准确',
        '请确认教室位置信息'
      ],
      warnings: [
        '某些课程信息可能需要人工确认'
      ],
      questions: [
        '请确认每节课的具体时间安排',
        '请确认学期开始和结束日期'
      ]
    };
    
  } catch (error) {
    console.error('AI分析文件失败:', error);
    
    // 如果AI分析失败，返回默认结果
    return {
      semesterInfo: {
        semester: '2024春季',
        startDate: '2024-02-26',
        endDate: '2024-06-30',
        totalWeeks: 18,
        classTimes: {
          morning: '08:00-08:45',
          afternoon: '14:00-14:45',
          evening: '19:00-19:45'
        },
        duration: 45
      },
      tableStructure: {
        weekArea: 'A2:E2',
        timeArea: 'A3:A12',
        timeArea2: '',
        courseArea: 'C3:H8',
        notesArea: 'A1:H1'
      },
      courses: [],
      confidence: 0.3,
      suggestions: [
        'AI分析失败，请检查文件格式或手动添加课程信息',
        '建议上传PDF、Excel或Word格式的课表文档'
      ],
      warnings: [
        'AI分析过程中出现错误，返回了默认示例数据',
        '请验证课程信息的准确性'
      ],
      questions: [
        'AI分析失败，无法提出具体问题'
      ]
    };
  }
}

// 取消合并单元格的函数
function unmergeCells(worksheet) {
  console.log('开始取消合并单元格...');
  
  const XLSX = require('xlsx');
  
  // 获取合并单元格信息
  const merges = worksheet['!merges'] || [];
  console.log('发现合并单元格数量:', merges.length);
  
  // 将工作表转换为二维数组
  const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1, defval: '' });
  
  // 处理每个合并单元格
  merges.forEach(merge => {
    const { s, e } = merge; // s: start, e: end
    const startRow = s.r;
    const startCol = s.c;
    const endRow = e.r;
    const endCol = e.c;
    
    // 获取合并单元格的值
    const mergedValue = jsonData[startRow] && jsonData[startRow][startCol] ? 
      jsonData[startRow][startCol].toString().trim() : '';
    
    if (mergedValue) {
      console.log(`处理合并单元格 [${startRow},${startCol}] 到 [${endRow},${endCol}]: ${mergedValue}`);
      
      // 将合并单元格的值填充到所有被合并的单元格中
      for (let row = startRow; row <= endRow; row++) {
        for (let col = startCol; col <= endCol; col++) {
          if (!jsonData[row]) {
            jsonData[row] = [];
          }
          jsonData[row][col] = mergedValue;
        }
      }
    }
  });
  
  console.log('合并单元格处理完成');
  return jsonData;
}

// 处理节次区域多列合并单元格的函数
function processTimeArea(jsonData, tableStructure) {
  try {
    console.log('开始处理节次区域多列合并单元格...');
    
    const timeArea = tableStructure.timeArea || 'A3:A12';
    const timeArea2 = tableStructure.timeArea2 || '';
    
    // 解析第一个节次区域
    const [timeStart, timeEnd] = timeArea.split(':');
    const timeColIndex = timeStart.charCodeAt(0) - 65;
    const timeStartRow = parseInt(timeStart.substring(1)) - 1;
    const timeEndRow = parseInt(timeEnd.substring(1)) - 1;
    
    let timeCol2Index = -1;
    let time2StartRow = -1;
    let time2EndRow = -1;
    
    // 解析第二个节次区域（如果存在）
    if (timeArea2) {
      const [time2Start, time2End] = timeArea2.split(':');
      timeCol2Index = time2Start.charCodeAt(0) - 65;
      time2StartRow = parseInt(time2Start.substring(1)) - 1;
      time2EndRow = parseInt(time2End.substring(1)) - 1;
    }
    
    console.log(`节次区域1: 第${timeColIndex + 1}列, ${timeStartRow}-${timeEndRow}行`);
    if (timeCol2Index >= 0) {
      console.log(`节次区域2: 第${timeCol2Index + 1}列, ${time2StartRow}-${time2EndRow}行`);
    }
    
    const processedTimeData = [];
    
    // 处理每一行的节次信息
    for (let row = timeStartRow; row <= timeEndRow; row++) {
      const timeValue1 = jsonData[row] && jsonData[row][timeColIndex] ? 
        jsonData[row][timeColIndex].toString().trim() : '';
      
      let timeValue2 = '';
      if (timeCol2Index >= 0 && jsonData[row] && jsonData[row][timeCol2Index]) {
        timeValue2 = jsonData[row][timeCol2Index].toString().trim();
      }
      
      // 合并两列的节次信息
      let combinedTimeValue = timeValue1;
      if (timeValue2 && timeValue2 !== timeValue1) {
        // 如果两列内容不同，合并它们
        combinedTimeValue = `${timeValue2} ${timeValue1}`.trim();
      }
      
      processedTimeData.push({
        row: row,
        timeValue: combinedTimeValue,
        originalTime1: timeValue1,
        originalTime2: timeValue2
      });
      
      console.log(`第${row + 1}行节次信息: ${combinedTimeValue} (原始: ${timeValue1} | ${timeValue2})`);
    }
    
    console.log('节次区域处理完成');
    return processedTimeData;
    
  } catch (error) {
    console.error('处理节次区域失败:', error);
    return [];
  }
}

// 基于表格结构提取课程信息的函数
async function extractCoursesFromStructure(jsonData, tableStructure, processedTimeData) {
  try {
    console.log('开始基于表格结构提取课程信息...');
    
    // 解析课程区域范围
    const courseArea = tableStructure.courseArea || 'C3:H8';
    const [startCell, endCell] = courseArea.split(':');
    const startCol = startCell.charCodeAt(0) - 65; // A=0, B=1, C=2...
    const startRow = parseInt(startCell.substring(1)) - 1; // 转换为0基索引
    const endCol = endCell.charCodeAt(0) - 65;
    const endRow = parseInt(endCell.substring(1)) - 1;
    
    console.log(`课程区域: ${startCol}-${endCol}列, ${startRow}-${endRow}行`);
    
    // 解析星期区域位置
    const weekArea = tableStructure.weekArea || 'A2:E2';
    const [weekStart, weekEnd] = weekArea.split(':');
    const weekStartCol = weekStart.charCodeAt(0) - 65;
    const weekRowIndex = parseInt(weekStart.substring(1)) - 1;
    
    console.log(`星期区域: ${weekStartCol}列, 第${weekRowIndex + 1}行`);
    
    // 解析备注区域位置
    const notesArea = tableStructure.notesArea || 'A1:H1';
    const [notesStart, notesEnd] = notesArea.split(':');
    const notesStartCol = notesStart.charCodeAt(0) - 65;
    const notesEndCol = notesEnd.charCodeAt(0) - 65;
    const notesRowIndex = parseInt(notesStart.substring(1)) - 1;
    
    console.log(`备注区域: ${notesStartCol}-${notesEndCol}列, 第${notesRowIndex + 1}行`);
    
    // 获取备注信息
    const notesInfo = [];
    for (let col = notesStartCol; col <= notesEndCol; col++) {
      const noteValue = jsonData[notesRowIndex] && jsonData[notesRowIndex][col] ? 
        jsonData[notesRowIndex][col].toString().trim() : '';
      if (noteValue) {
        notesInfo.push(noteValue);
      }
    }
    console.log('备注信息:', notesInfo.join(' | '));
    
    const courses = [];
    let courseId = 1;
    
    // 遍历课程区域的每个单元格（按列遍历）
    for (let col = startCol; col <= endCol; col++) {
      for (let row = startRow; row <= endRow; row++) {
        const cellValue = jsonData[row] && jsonData[row][col] ? jsonData[row][col].toString().trim() : '';
        
        // 检查课程单元格是否为空
        if (!cellValue || cellValue === '' || cellValue.toLowerCase() === 'null') {
          console.log(`跳过空单元格 [${row},${col}]`);
          continue;
        }
        
        // 检查对应的星期信息是否为空
        const weekCol = weekStartCol + (col - startCol);
        const weekValue = jsonData[weekRowIndex] && jsonData[weekRowIndex][weekCol] ? 
          jsonData[weekRowIndex][weekCol].toString().trim() : '';
        
        if (!weekValue || weekValue === '') {
          console.log(`跳过星期信息为空的单元格 [${row},${col}]`);
          continue;
        }
        
        console.log(`处理单元格 [${row},${col}]: ${cellValue}`);
        console.log(`对应星期信息: ${weekValue}`);
        
        // 从处理后的节次数据中获取节次信息
        const timeData = processedTimeData.find(t => t.row === row);
        const timeValue = timeData ? timeData.timeValue : '';
        
        console.log(`节次信息: ${timeValue}`);
        
        // 计算星期几（基于列位置）
        const dayOfWeek = col - startCol + 1;
        
        // 计算节次（基于行位置）
        const timeSlot = row - startRow + 1;
        
        // 使用AI分析单元格内容，判断是否包含多门课程
        const cellAnalysis = await analyzeCellContent(cellValue, weekValue, timeValue, '', notesInfo, dayOfWeek, timeSlot);
        
        if (cellAnalysis && cellAnalysis.length > 0) {
          cellAnalysis.forEach(course => {
            courses.push({
              id: `course_${courseId++}`,
              name: course.name || '未命名课程',
              teacher: course.teacher || '待定',
              classroom: course.classroom || '待定',
              weeks: course.weeks || [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
              dayOfWeek: dayOfWeek,
              timeSlot: timeSlot,
              duration: course.duration || 2,
              credits: course.credits || 3,
              type: course.type || 'required',
              description: course.description || '',
              cellPosition: `${String.fromCharCode(65 + col)}${row + 1}`
            });
          });
        }
      }
    }
    
    console.log(`提取完成，共${courses.length}门课程`);
    return courses;
    
  } catch (error) {
    console.error('提取课程信息失败:', error);
    return [];
  }
}

// 分析单个单元格内容的函数
async function analyzeCellContent(cellValue, weekValue, timeValue, timeValue2, notesInfo, dayOfWeek, timeSlot) {
  try {
    console.log(`开始分析单元格内容: ${cellValue.substring(0, 100)}...`);
    
    const prompt = `请仔细分析以下课表单元格内容，可能包含一门或多门课程：

单元格内容: "${cellValue}"
星期信息: "${weekValue}"
节次信息: "${timeValue}"
备注信息: "${notesInfo.join(' | ')}"
星期几: ${dayOfWeek}
节次: ${timeSlot}

请按照以下步骤分析：

1. 首先判断单元格内容是否包含多门课程：
   - 通过换行符（\\n、\\r\\n）分隔
   - 通过分号（;）分隔
   - 通过重复的课程信息模式识别
   - 例如：如果看到两个不同的周次范围（如7-14周和15-17周），很可能是两门课程
   - 如果看到不同的课程名称，很可能是多门课程

2. 如果包含多门课程，请拆分成多个课程对象，每门课程包含：
   - 课程名称（只保留课程名，去除节次、周次、教室、教师等信息）
   - 任课教师
   - 教室位置
   - 周次范围（转换为数组格式，如[7,8,9,10,11,12,13,14]）
   - 课程类型（必修/选修/实践）
   - 学分
   - 持续时间（节数）

3. 特别注意：
   - 课程名称应该简洁明了，如"数据分析与可视化"
   - 不要将整个单元格内容作为课程名称
   - 每门课程应该有独立的周次范围
   - 如果课程区域中的课程信息包括了节次信息，那么就以课程区域中的课程信息为标准
   - 如果信息不明确，请使用合理的默认值
   - 备注区域中可能会存在课程信息，记得记录

4. 如果只有一门课程，也要按照标准格式返回

请返回JSON数组格式：
[
  {
    "name": "课程名称",
    "teacher": "教师姓名", 
    "classroom": "教室",
    "weeks": [7,8,9,10,11,12,13,14],
    "duration": 2,
    "credits": 3,
    "type": "required",
    "description": "课程描述"
  }
]`;

    const result = await callAliyunAI(prompt);
    console.log(`单元格分析结果:`, result);
    
    // 确保返回的是数组
    if (Array.isArray(result)) {
      return result;
    } else if (result && typeof result === 'object') {
      return [result];
    } else {
      console.log('AI返回结果格式异常，使用默认处理');
      return [{
        name: cellValue.split('\n')[0].split('/')[0].trim(),
        teacher: '待定',
        classroom: '待定',
        weeks: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
        duration: 2,
        credits: 3,
        type: 'required',
        description: ''
      }];
    }
    
  } catch (error) {
    console.error('分析单元格内容失败:', error);
    return [{
      name: cellValue.split('\n')[0].split('/')[0].trim(),
      teacher: '待定',
      classroom: '待定',
      weeks: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16],
      duration: 2,
      credits: 3,
      type: 'required',
      description: ''
    }];
  }
}

// 调用阿里云大模型API
async function callAliyunAI(prompt) {
  try {
    const axios = require('axios');
    const apiKey = process.env.ALIYUN_API_KEY;
    const model = process.env.ALIYUN_MODEL || 'qwen-flash';
    
    if (!apiKey) {
      console.error('阿里云API密钥未配置');
      throw new Error('AI服务未配置');
    }
    
    const messages = [
      {
        role: 'system',
        content: '你是一个专业的课表分析助手。请仔细分析用户提供的课表文档，提取准确的课程信息，并返回标准的JSON格式结果。'
      },
      {
        role: 'user',
        content: prompt
      }
    ];

    console.log('调用阿里云大模型API...');
    console.log('API密钥:', apiKey ? `${apiKey.substring(0, 10)}...` : '未配置');
    console.log('模型:', model);
    console.log('消息内容:', JSON.stringify(messages, null, 2));
    
    // 调用阿里云大模型API
    const response = await axios.post('https://dashscope.aliyuncs.com/api/v1/services/aigc/text-generation/generation', {
      model: model,
      input: {
        messages: messages
      },
      parameters: {
        max_tokens: 4000,
        temperature: 0.3
      }
    }, {
      headers: {
        'Authorization': `Bearer ${apiKey}`,
        'Content-Type': 'application/json'
      },
      timeout: 50000 // 设置50秒超时
    });
    
    console.log('阿里云API响应状态:', response.status);
    console.log('阿里云API响应数据:', JSON.stringify(response.data, null, 2));

    if (response.data && response.data.output && response.data.output.text) {
      const aiResponse = response.data.output.text;
      console.log('AI原始响应:', aiResponse);
      
      // 尝试解析JSON响应
      try {
        // 提取JSON部分（去除可能的markdown格式）
        const jsonMatch = aiResponse.match(/\{[\s\S]*\}/);
        if (jsonMatch) {
          let jsonStr = jsonMatch[0];
          
          // 如果JSON被截断，尝试修复
          if (!jsonStr.endsWith('}')) {
            console.log('检测到JSON被截断，尝试修复...');
            // 找到最后一个完整的对象
            const lastCompleteMatch = jsonStr.match(/\{[\s\S]*"courses":\s*\[[\s\S]*?\]\s*\}/);
            if (lastCompleteMatch) {
              jsonStr = lastCompleteMatch[0];
            } else {
              // 如果还是找不到完整对象，至少尝试修复基本的JSON结构
              jsonStr = jsonStr + '}';
            }
          }
          
          const result = JSON.parse(jsonStr);
          console.log('解析后的JSON结果:', result);
          return result;
        } else {
          throw new Error('AI响应中未找到有效的JSON格式');
        }
      } catch (parseError) {
        console.error('解析AI响应JSON失败:', parseError);
        console.log('AI响应内容:', aiResponse);
        throw new Error('AI响应格式错误');
      }
    } else {
      console.error('阿里云API响应格式异常:', response.data);
      throw new Error('AI服务响应异常');
    }
    
  } catch (error) {
    console.error('调用阿里云大模型失败:', error);
    throw error;
  }
}

module.exports = router;
